package com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.repository.impl;

import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.vo.CasualtiesCalculationVO;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.param.AssessParam;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.entity.CasualtiesPE;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.entity.CasualtiesPECalculation;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.param.CasualtiesPECalculationParam;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.repository.CasualtiesPERepository;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.vo.CasualtiesPCCPEVO;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.casualtiesPE.vo.CasualtiesXYPEVO;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.resistEarthquakesPE.entity.ResistEarthquakesPECalculation;
import com.css.fxfzfxqh.modules.largeScreen.entity.Calculation;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/06/15
 * @Description: 人员伤亡概率评估Repository
 */
@Repository
public class CasualtiesPERepositoryImpl implements CasualtiesPERepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    //抗人员伤亡_确定评估_评估记录主表
    private static final String TABLE_NAME = "fxqh_casualties_assess_pd";
    //确定评估_人员伤亡_计算
    private static final String TABLE_CALCULATION = "fxqh_casualties_calculation_pd";

    //新增评估记录
    @Override
    public void addAssess(CasualtiesPE param) {
        String sql = "insert into " + TABLE_NAME + "(id,assess_code,assess_name,assess_desc,house_data,house_data_flag," +
                "coordinate_range,calculation,create_user,create_time,del_flag,seismic_input_mode) " +
                "values(:id,:assessCode,:assessName,:assessDesc,:houseData,:houseDataFlag," +
                ":coordinateRange,:calculation,:createUser,:createTime,:delFlag,:seismicInputMode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //根据id查询
    @Override
    public CasualtiesPE findById(String id) {
        String sql = "select * from " + TABLE_NAME + " where id = ? ";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CasualtiesPE.class), id);
    }

    //修改评估记录
    @Override
    public void upDateAssess(CasualtiesPE param) {
        String sql = "update " + TABLE_NAME +
                " set assess_name = :assessName,assess_desc = :assessDesc," +
                "update_user=:updateUser,update_time=:updateTime where del_flag = '0' and id = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //查询评估记录最新code
    @Override
    public String queryLatest(StringBuilder code) {
        StringBuilder sql = new StringBuilder("select assess_code from " + TABLE_NAME + " where del_flag = '0' and assess_code like '%" + code + "%' order by create_time desc limit 1");
        try {
            return jdbcTemplate.queryForObject(sql.toString(), String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, AssessParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by geography_id ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //评估记录编号
        if (!PlatformObjectUtils.isEmpty(param.getAssessCode())) {
            whereSql.append(" and assess_code = '" + param.getAssessCode() + "'");
        }
        //评估记录名称
        if (!PlatformObjectUtils.isEmpty(param.getAssessName())) {
            whereSql.append(" and assess_name = '" + param.getAssessName() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CasualtiesPE.class)).size();
        List<CasualtiesPE> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(CasualtiesPE.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //批量新增计算着
    @Override
    public void batchCalculation(List<CasualtiesPECalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,casualties,time,extreme,calculated_value,correction_value,calculated_value_pt," +
                "correction_value_pt,chance_pt,expect_value,coordinate_range,create_user,create_time,years,del_flag,risk,seismic_input_mode,extends1,house_data_flag,geom,sort)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:casualties,:time,:extreme,:calculatedValue,:correctionValue,:calculatedValuePt," +
                ":correctionValuePt,:chancePt,:expectValue,:coordinateRange,:createUser,:createTime,:years,:delFlag,:risk,:seismicInputMode,:extends1,:houseDataFlag,ST_GeometryFromText(:geom,4490),:sort)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    //查询省市县
    @Override
    public List<CasualtiesPCCPEVO> getListPCC(CasualtiesPECalculationParam param) {
        StringBuilder sql = new StringBuilder("select province_name," +
                "city_name," +
                "county_name," +
                "geography_id," +
                "casualties," +
                "time," +
                "extreme," +
                "calculated_value," +
                "correction_value," +
                "expect_value  from " + TABLE_CALCULATION + " where del_flag= '0' ");
        //任务id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            sql.append(" and assess_id = '" + param.getId() + "'");
        }
        //重伤死亡 重伤1 死亡2 ；
        if (!PlatformObjectUtils.isEmpty(param.getCasualties())) {
            // if ("rysw1".equals(param.getCasualties())) {
            //     param.setCasualties("1");
            // } else {
            //     param.setCasualties("2");
            // }
            sql.append(" and casualties = '" + param.getCasualties() + "'");
        }
        //t年
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            sql.append(" and years = '" + param.getYears() + "'");
        }

        //时间白天晚上 白天1 晚上 0；
        if (!PlatformObjectUtils.isEmpty(param.getTime())) {
            // if ("sjd1".equals(param.getTime())) {
            //     param.setTime("1");
            // } else {
            //     param.setTime("0");
            // }
            sql.append(" and time = '" + param.getTime() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            sql.append(" and province_name = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
            sql.append(" and city_name = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())) {
            sql.append(" and county_name = '" + param.getCounty() + "'");
        }
        return jdbcTemplate.query(sql.append(" order by geography_id ,sort asc").toString(),
                new BeanPropertyRowMapper<>(CasualtiesPCCPEVO.class));
    }

    //查询坐标范围
    @Override
    public List<CasualtiesXYPEVO> getListXY(CasualtiesPECalculationParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION + " where del_flag= '0' ");
        //任务id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            sql.append(" and assess_id = '" + param.getId() + "'");
        }
        //重伤死亡 重伤1 死亡2 ；
        if (!PlatformObjectUtils.isEmpty(param.getCasualties())) {
            if ("rysw1".equals(param.getCasualties())) {
                param.setCasualties("1");
            } else {
                param.setCasualties("2");
            }
            sql.append(" and casualties = '" + param.getCasualties() + "'");
        }
        //t年
        if (!PlatformObjectUtils.isEmpty(param.getYears())) {
            sql.append(" and years = '" + param.getYears() + "'");
        }

        //时间白天晚上 白天1 晚上 0；
        if (!PlatformObjectUtils.isEmpty(param.getTime())) {
            if ("sjd1".equals(param.getTime())) {
                param.setTime("1");
            } else {
                param.setTime("0");
            }
            sql.append(" and time = '" + param.getTime() + "'");
        }
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CasualtiesXYPEVO.class));
    }

    //根据条件查询计算值
    @Override
    public Map<String, Object> queryCalculation(int curPage, int pageSize, String id, String casualties, String time, String years) {
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION + " where del_flag= '0' ");
        //任务id
        if (!PlatformObjectUtils.isEmpty(id)) {
            sql.append(" and assess_id = '" + id + "'");
        }
        //重伤死亡 重伤1 死亡2 ；
        if (!PlatformObjectUtils.isEmpty(casualties)) {
            sql.append(" and casualties = '" + casualties + "'");
        }
        //t年
        if (!PlatformObjectUtils.isEmpty(years)) {
            sql.append(" and years = '" + years + "'");
        }

        //时间白天晚上 白天1 晚上 0；
        if (!PlatformObjectUtils.isEmpty(time)) {
            sql.append(" and time = '" + time + "'");
        }
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CasualtiesPECalculation.class)).size();
        List<CasualtiesPECalculation> list = jdbcTemplate.query(sql.append(" order by geography_id,sort asc ").append(limitSql).toString(), new BeanPropertyRowMapper<>(CasualtiesPECalculation.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //修改概率P(t)修正值
//    @Override
//    public void updateCorrectionValue(CasualtiesPECalculation param) {
//        String sql = "update " + TABLE_CALCULATION + " set correction_value=:correctionValue , correction_value_pt =:correctionValuePt," +
//                "update_user = :updateUser ,update_time = :updateTime,expect_value = :expectValue where id = :id";
//        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
//        namedParameterJdbcTemplate.update(sql, source);
//    }
    //修改概率P(t)修正值
    @Override
    public void updateCorrectionValue(CasualtiesPECalculation param) {
        String sql = "update " + TABLE_CALCULATION + " set correction_value=:correctionValue ,expect_value=:expectValue," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void deleteEvaluateResult(String assessId) {
        jdbcTemplate.update("delete from fxqh_casualties_calculation_pd where assess_id='" + assessId + "'");
    }

    @Override
    public List<Map<String, Object>> getSumByDivisionAndAssessCodes(String province, String city, String county,
                                                                    String assessCodes, String condition) {
        String sql = "SELECT SUM(CAST(t1.chance_pt AS FLOAT)) AS SUM, COUNT(t1.assess_id) AS COUNT FROM fxqh_casualties_calculation_pd t1 LEFT JOIN ( SELECT * FROM fxqh_calculation_tasks WHERE del_flag = '0' AND id='" + assessCodes + "') t2 ON t1.assess_id = t2.ID WHERE t1.province_name = ? AND t1.city_name = ? AND t1.county_name = ? AND t1.years = ? AND t2.del_flag = '0' AND time = '0' AND casualties = '2' GROUP BY t1.extends1 ORDER BY COUNT(t1.assess_id) DESC";
        return jdbcTemplate.queryForList(sql, province, city, county, condition);
    }

    @Override
    public List<Map<String, Object>> getSumByTaskId(String taskId) {
        String sql = "SELECT SUM(CAST(t1.correction_value AS FLOAT)) AS SUM, COUNT(t1.assess_id) AS COUNT, t1.province_name as provinceName, t1.city_name as cityName,t1.county_name as countyName, t1.years as years" +
                " FROM fxqh_casualties_calculation_pd t1" +
                " WHERE time = 'sjd2' AND casualties = 'rysw2' and assess_id='" + taskId + "'" +
                " GROUP BY t1.province_name, t1.city_name,t1.county_name, t1.years,t1.extends1 ORDER BY COUNT(t1.assess_id) DESC";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<CasualtiesPECalculation> getOtherConditionData(String assessId, String geographyId, String casualties, String time, String id) {
       String sql = "select * from fxqh_casualties_calculation_pd where assess_id='"+assessId+"' and geography_id='"+geographyId+"' and casualties='"+casualties+"' and time='"+time+"' and id!='"+id+"'";
       return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CasualtiesPECalculation.class));
    }

    @Override
    public void updateExpectValue(CasualtiesPECalculation casualtiesPECalculation) {
        jdbcTemplate.update("UPDATE fxqh_casualties_calculation_pd set expect_value='"+casualtiesPECalculation.getExpectValue()+"' where id='"+casualtiesPECalculation.getId()+"'");
    }

    @Override
    public List<CasualtiesPECalculation> getCasualtiesResult(String taskId, String province, String casualties, String time) {
        String sql = "select * from fxqh_casualties_calculation_pd where assess_id='"+taskId+"'  and casualties='"+casualties+"' and time='"+time+"' and province_name='"+province+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CasualtiesPECalculation.class));
    }

    @Override
    public List<Calculation> getValue(List<String> taskIdList, String code, String type) {
        String sql = "";
        if("0".equals(code)){
            if("1".equals(type)){
                sql = "select sum(correction_value) val,province_name areaName from fxqh_casualties_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"') and casualties='rysw2'  group by province_name order by val";
            }else{
                sql = "select sum(correction_value) val,province_name areaName from fxqh_casualties_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"') and casualties='rysw1'  group by province_name order by val";
            }
        }else{
            if("1".equals(type)){
                sql = "select sum(correction_value) val,city_name as area_name from fxqh_casualties_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"') and casualties='2' GROUP BY area_name order by val";
            }else{
                sql = "select sum(correction_value) val,city_name as area_name from fxqh_casualties_calculation_pd where assess_id in ('"+String.join("','", taskIdList)+"') and casualties='1' GROUP BY area_name order by val";
            }
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Calculation.class));
    }
}
